﻿Imports Telerik.Web.UI
Imports System.Data.SqlClient
Imports System.Data
Imports System.IO

Partial Class Voucher
    Inherits System.Web.UI.Page
    Dim _CommandName As String
    Dim _Command As String
    Dim Grid As RadGrid
    Dim VoucherObj As New DBVoucherSP
    Dim VoucherDatatset As New Data.DataSet
    Dim VoucherLinesObj As New DBVoucherLinesSP

    Dim VoucherLinesDatatset As New Data.DataSet
    Dim AccountObj As New AccountsSP
    Dim AccountDataSet As New DataSet
    Dim FilesObj As New DBtblFilesSP
    Dim FilesDatatset As New Data.DataSet
    Public strConnection As String = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ConnectionString.ToString
    Dim index As Integer = -1

    Protected Sub Page_PreInit(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreInit
        Me.Theme = GetGlobalResourceObject("SystemResource", "Theme")
    End Sub

    Private Sub rgVoucher_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles rgVoucher.PreRender

        For Each item In rgVoucher.MasterTableView.Items
            If TypeOf item Is GridEditableItem Then
                Dim editableItem As GridEditableItem = TryCast(item, GridDataItem)

                If DirectCast(editableItem("RecordStatus").Controls(0), HyperLink).Text = "2" Then
                    'rgVoucherLines.MasterTableView.Items(index).BackColor = Drawing.Color.Yellow
                    editableItem.BackColor = Drawing.Color.Yellow
                    'editableItem("ReturnColumn").Enabled = True
                ElseIf DirectCast(editableItem("RecordStatus").Controls(0), HyperLink).Text = "0" Or DirectCast(editableItem("RecordStatus").Controls(0), HyperLink).Text = "" Then
                    'rgVoucherLines.MasterTableView.Items(index).BackColor = Drawing.Color.Yellow
                    'editableItem("ReturnColumn").Enabled = False
                End If 'End If
            End If
        Next

    End Sub

    Protected Sub rgLines_ItemDataBound(ByVal sender As Object, ByVal e As GridItemEventArgs)
        If TypeOf e.Item Is GridDataItem Then
            Dim dataItem As GridDataItem = CType(e.Item, GridDataItem)
            Dim AccountID As Integer = dataItem("AccountNo").Text
            AccountDataSet = AccountObj.QueryDatabase(DBParentStoredProcedureClass.SqlQueryType.FillDataset, "GetAccount", AccountID, , , , )
            If AccountDataSet IsNot Nothing Then
                dataItem("Account_Name").Text = AccountDataSet.Tables(0).Rows(0).Item("AccountNameAr")
                dataItem("Account_Num").Text = AccountDataSet.Tables(0).Rows(0).Item("AccountNo")
            End If
        End If
    End Sub

    Protected Sub rcbAccount_ItemsRequested(ByVal sender As Object, ByVal e As RadComboBoxItemsRequestedEventArgs)
        Dim sql As String = "SELECT ID as AccountNo, isnull([AccountNo],0) as [Account_Num], isnull([AccountNameAr],'-') as [AccountNameAr] FROM [Accounts] where (AccountNo like @Account_Num + '%' or  @Account_Num ='')  or (AccountNameAr like @AccountNameAr + '%' or  @AccountNameAr ='')"
        Dim adapter As New SqlDataAdapter(sql, strConnection)
        adapter.SelectCommand.Parameters.AddWithValue("@AccountNameAr", e.Text)
        adapter.SelectCommand.Parameters.AddWithValue("@Account_Num", e.Text)
        Dim dt As New DataTable()
        adapter.Fill(dt)
        Dim comboBox As RadComboBox = DirectCast(sender, RadComboBox)
        ' Clear the default Item that has been re-created from ViewState at this point.
        comboBox.Items.Clear()
        For Each row As DataRow In dt.Rows
            Dim item As New RadComboBoxItem()
            item.Text = row("AccountNameAr").ToString()
            item.Value = row("AccountNo").ToString()
            item.Attributes.Add("Account_Num", row("Account_Num").ToString())
            item.Attributes.Add("AccountNameAr", row("AccountNameAr").ToString())
            item.Attributes.Add("AccountNo", row("AccountNo").ToString())
            comboBox.Items.Add(item)
            item.DataBind()
        Next
        Session("ChangeItem") = "1"   ' This is to be able to change The Item in the Update Mode Otherwise, it will Load the old Item
    End Sub

    Protected Sub OnSelectedIndexChangedHandler(ByVal sender As Object, ByVal e As RadComboBoxSelectedIndexChangedEventArgs)
        Session("AccountNo") = e.Value
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            Dim _lblPageTitle As Label
            _lblPageTitle = Me.Master.FindControl("lblPageTitle")
            _lblPageTitle.Text = GetLocalResourceObject("PageTitle")
            rdpDate.DbSelectedDate = DateTime.Now()
            rtbYear.Text = Year(Now()).ToString()
        End If
    End Sub

    Protected Sub rgVoucher_ItemCommand(ByVal source As Object, ByVal e As GridCommandEventArgs) Handles rgVoucher.ItemCommand
        _CommandName = e.CommandName
        If e.CommandName = RadGrid.ExpandCollapseCommandName And TypeOf e.Item Is GridDataItem Then
            DirectCast(e.Item, GridDataItem).ChildItem.FindControl("InnerContainer").Visible = Not e.Item.Expanded
        End If
        If _CommandName = "Add" Then
            e.Canceled = True
            hdnVoucherID.Value = "0"
            Session("VoucherID") = "0"
            ResetControls()
            rdpDate.DbSelectedDate = Now    'Default Document Date is Today
            rtbYear.Text = Year(Now()).ToString()         'Default Year is the Current Year
            pnlVoucher.Visible = True
            rgVoucher.Visible = False
            _CommandName = Nothing
            Exit Sub
        ElseIf _CommandName = "Edit" Then
            For Each item In rgVoucher.MasterTableView.Items
                Dim dataitem As GridDataItem = CType(item, GridDataItem)
                If dataitem.Selected = True Then
                    e.Canceled = True
                    LoadControls()
                    pnlVoucher.Visible = True
                    pnlVoucher.Visible = True
                    rgVoucher.Visible = False
                    _CommandName = Nothing
                    Exit Sub
                End If
            Next
        End If
        If _CommandName = "ExportColumn" Then

            'If TypeOf e.Item Is GridDataItem Then
            'Dim dataItem As GridDataItem = CType(e.Item, GridDataItem)
            For Each item In rgVoucher.MasterTableView.Items
                If TypeOf item Is GridDataItem Then
                    Dim editableItem As GridDataItem = TryCast(item, GridDataItem)
                    If editableItem.Selected = True Then
                        Dim id As String = editableItem.GetDataKeyValue("ID").ToString()
                        VoucherDatatset = VoucherObj.QueryDatabase(DBParentStoredProcedureClass.SqlQueryType.FillDataset, "Export", id, , , , , , , , )
                        Exit For
                    End If
                End If
            Next
            e.Canceled = True
            '_CommandName = "RebindGrid"
            'rgVoucher.DataSourceID = "odsVoucher"
            'rgVoucher.DataBind()
        End If

        If _CommandName = "ReturnColumn" Then

            If TypeOf e.Item Is GridDataItem Then
                Dim dataItem As GridDataItem = CType(e.Item, GridDataItem)
                For Each item In rgVoucher.MasterTableView.Items
                    If TypeOf item Is GridDataItem Then
                        Dim editableItem As GridDataItem = TryCast(item, GridDataItem)
                        If editableItem.Selected = True Then
                            Dim id As String = editableItem.GetDataKeyValue("ID").ToString()
                            VoucherDatatset = VoucherObj.QueryDatabase(DBParentStoredProcedureClass.SqlQueryType.FillDataset, "Return", id, , , , , , , , )
                            Exit For
                        End If
                    End If
                Next
                e.Canceled = True
                '_CommandName = "RebindGrid"
            End If
        End If
    End Sub

    Protected Sub rgAttachments_ItemDataBound(ByVal sender As Object, ByVal e As GridItemEventArgs)
        If TypeOf e.Item Is GridDataItem Then
            Dim dataItem As GridDataItem = CType(e.Item, GridDataItem)
            Dim IDN As String = dataItem("Voucher_IDN").Text
            Dim FileID As String = dataItem("FileID").Text
            Dim IDC As String = "V"
            Dim hyperLink As HyperLink = DirectCast(dataItem("OpenAttachment").Controls(0), HyperLink)
            hyperLink.NavigateUrl = "~/UserControls/DisplayForm.aspx?Voucher_IDC=" + IDC + "&Voucher_IDN=" + IDN + "&FileID=" + FileID
        End If
    End Sub

    Protected Sub rgVoucher_ItemCreated(ByVal sender As Object, ByVal e As GridItemEventArgs) Handles rgVoucher.ItemCreated
        If TypeOf e.Item Is GridNestedViewItem Then
            e.Item.FindControl("InnerContainer").Visible = (DirectCast(e.Item, GridNestedViewItem)).ParentItem.Expanded
        End If
    End Sub

    Protected Sub rgVoucher_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles rgVoucher.SelectedIndexChanged
        For Each item As GridDataItem In rgVoucher.SelectedItems
            item = DirectCast(rgVoucher.SelectedItems(0), GridDataItem)
            hdnVoucherID.Value = item("ID").Text.ToString()
            Session("VoucherID") = item("ID").Text.ToString()
        Next
    End Sub

#Region "Attachments"


    Private Sub UploadAttachments()

        Dim FileUpload As RadAsyncUpload = AsyncUploadVoucher

        lblUploadError.Visible = False
        If Not (FileUpload.UploadedFiles.Count > 0) Then
            'CType(Form.FindControl("lblUploadError"), Label).Visible = False
            'lblUploadError.Visible = False
            Return
        End If

        For i As Integer = 0 To FileUpload.UploadedFiles.Count - 1


            Dim fs As System.IO.Stream = FileUpload.UploadedFiles(i).InputStream

            Dim imageBytes As [Byte]() = New [Byte](fs.Length - 1) {}
            fs.Read(imageBytes, 0, CInt(fs.Length))
            fs.Close()



            'FilesDatatset = FilesObj.QueryDatabase(DBParentStoredProcedureClass.SqlQueryType.FillDataset, "RtvFile", , , CType(Form.FindControl("hdnVoucherID"), HiddenField).Value.ToString(), , , )
            Dim strQuery As [String]

            'If FilesDatatset.Tables(0).Rows.Count > 0 Then
            'insert the file into database
            'FilesDatatset = FilesObj.QueryDatabase(DBParentStoredProcedureClass.SqlQueryType.FillDataset, "SaveFile", , , , , , )
            'strQuery = "Update tblFiles set [Name] = @Name, [ContentType] = @ContentType , [Data] = @Data where [Voucher_IDC] = @Voucher_IDC and [Voucher_IDN] = @Voucher_IDN "
            'Else
            'insert the file into database
            strQuery = "insert into tblFiles([Voucher_IDC], [Voucher_IDN],[Name], [ContentType], [Data]) values (@Voucher_IDC, @Voucher_IDN,@Name, @ContentType, @Data)"
            'End If


            Dim cmd As New SqlCommand(strQuery)
            cmd.Parameters.AddWithValue("@Voucher_IDC", "V")
            cmd.Parameters.AddWithValue("@Voucher_IDN", hdnVoucherID.Value.ToString())
            cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = FileUpload.UploadedFiles(i).GetName()

            'Make sure we are dealing with a Compatible File Format Like JPG or GIF file
            Dim extension As String = System.IO.Path.GetExtension(FileUpload.UploadedFiles(i).GetName()).ToLower()
            Dim MIMEType As String = Nothing
            Select Case extension
                Case ".gif"
                    MIMEType = "image/gif"
                    Exit Select
                Case ".jpg", ".jpeg", ".jpe"
                    MIMEType = "image/jpeg"
                    Exit Select
                Case ".png"
                    MIMEType = "image/png"
                    Exit Select
                Case ".bmp"
                    MIMEType = "image/bmp"
                    Exit Select
                Case ".txt"
                    MIMEType = "application/txt"
                    Exit Select
                Case ".pdf"
                    MIMEType = "application/pdf"
                    Exit Select
                Case ".doc"
                    MIMEType = "application/msword"
                    Exit Select
                Case ".xls"
                    MIMEType = "application/msexcel"
                    Exit Select
                Case ".docx"
                    MIMEType = "application/vnd.ms-word"
                    Exit Select
                Case ".xlsx"
                    MIMEType = "application/vnd.ms-excel"
                    Exit Select
                Case Else
                    '   Invalid file type uploaded
                    'CType(Form.FindControl("lblUploadError"), Label).Visible = True
                    lblUploadError.Visible = True
                    Return
            End Select

            cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value = MIMEType
            cmd.Parameters.Add("@Data", SqlDbType.VarBinary).Value = imageBytes
            ' bytes;
            InsertUpdateData(cmd)

        Next

    End Sub

    Private Function InsertUpdateData(ByVal cmd As SqlCommand) As [Boolean]
        Dim con As New SqlConnection(strConnection)
        cmd.CommandType = CommandType.Text
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
            Return True
        Catch ex As Exception
            Response.Write(ex.Message)
            Return False
        Finally
            con.Close()
            con.Dispose()
        End Try
    End Function


#End Region

#Region "Toolbar Functions"


    Sub ResetControls()

        ' Form = EmployeesForm
        Grid = rgVoucher

        Dim x As Integer = 0

        Dim ctr As Control

        'For Each ctr In Form.Controls
        For Each ctr In pnlVoucher.Controls

            If (ctr.[GetType]().ToString() = "Telerik.Web.UI.RadTextBox") Then
                CType(ctr, RadTextBox).Text = ""
            ElseIf (ctr.[GetType]().ToString() = "Telerik.Web.UI.RadNumericTextBox") Then
                CType(ctr, RadNumericTextBox).Text = ""
            ElseIf (ctr.[GetType]().ToString() = "ASP.usercontrols_onefiltereddropdownlist_ascx") Then
                CType(ctr.FindControl("RadComboBoxDescription"), Telerik.Web.UI.RadComboBox).SelectedIndex = 0
            ElseIf (ctr.[GetType]().ToString() = "Telerik.Web.UI.RadComboBox") Then
                CType(ctr, RadComboBox).SelectedIndex = 0
            ElseIf (ctr.[GetType]().ToString() = "Telerik.Web.UI.RadDatePicker") Then
                CType(ctr, RadDatePicker).DbSelectedDate = DBNull.Value
            End If

        Next

    End Sub

    Sub SaveControls()
        hdnVoucherID.Value = VoucherObj.QueryDatabase(DBParentStoredProcedureClass.SqlQueryType.ExecuteScalar, "SaveVoucher", _
                                                      hdnVoucherID.Value, rtbYear.Text, rtbVoucherNo.Text, rdpDate.DbSelectedDate, rcbType.SelectedValue, _
                                                      rcbMainCostCenter.SelectedValue, rcbCostCenter.SelectedValue, , rtbRemarks.Text)
        SaveDtl()
        UploadAttachments()
    End Sub

    Protected Sub SaveDtl()
        Dim regDate As Date = Date.Now()
        Dim strDate As String = regDate.ToString("ddMMMyyyy")

        For counter = 0 To rgVoucherLines.Items.Count - 1
            VoucherLinesObj.QueryDatabase(DBParentStoredProcedureClass.SqlQueryType.ExecuteNonQuery, "SaveVoucherLine", rgVoucherLines.Items(counter)("ID").Text, _
            hdnVoucherID.Value, _
            IIf(rgVoucherLines.Items(counter)("TransYear").Text = "&nbsp;", "", rgVoucherLines.Items(counter)("TransYear").Text), _
            IIf(rgVoucherLines.Items(counter)("PayOrderNo").Text = "&nbsp;", "", rgVoucherLines.Items(counter)("PayOrderNo").Text), _
             IIf(rgVoucherLines.Items(counter)("ProgramNo").Text = "&nbsp;", "", rgVoucherLines.Items(counter)("ProgramNo").Text), _
             IIf(rgVoucherLines.Items(counter)("AccountNo").Text = "&nbsp;", "", rgVoucherLines.Items(counter)("AccountNo").Text), _
             IIf(rgVoucherLines.Items(counter)("CostCenter_ID").Text = "&nbsp;", "", rgVoucherLines.Items(counter)("CostCenter_ID").Text), _
             IIf(rgVoucherLines.Items(counter)("Debit").Text = "&nbsp;", "", rgVoucherLines.Items(counter)("Debit").Text), _
             IIf(rgVoucherLines.Items(counter)("Credit").Text = "&nbsp;", "", rgVoucherLines.Items(counter)("Credit").Text), _
            CType(rgVoucherLines.Items(counter)("RecordStatus").Controls(0), HyperLink).Text, _
            Session("UserName").ToString(), Now, _
             IIf(rgVoucherLines.Items(counter)("Remarks").Text = "&nbsp;", "", rgVoucherLines.Items(counter)("Remarks").Text))
        Next
    End Sub

    Sub DeleteRecord()
        Dim VoucherID As String = Session("VoucherID").ToString()
        VoucherDatatset = VoucherObj.QueryDatabase(DBParentStoredProcedureClass.SqlQueryType.FillDataset, "DeleteVoucher", hdnVoucherID.Value, , , , , , , , )
        Session("VoucherID") = Nothing
    End Sub

    Sub LoadControls()
        Grid = rgVoucher
        VoucherDatatset = VoucherObj.QueryDatabase(DBParentStoredProcedureClass.SqlQueryType.FillDataset, "RtvSelectedVoucher", Session("VoucherID").ToString(), , , , , , , , )
        rtbYear.Text = VoucherDatatset.Tables(0).Rows(0).Item("Year").ToString()
        hdnVoucherID.Value = VoucherDatatset.Tables(0).Rows(0).Item("ID").ToString()
        rcbType.SelectedValue = VoucherDatatset.Tables(0).Rows(0).Item("VoucherType").ToString()
        rtbVoucherNo.Text = VoucherDatatset.Tables(0).Rows(0).Item("VoucherNo").ToString()
        rcbCostCenter.SelectedValue = VoucherDatatset.Tables(0).Rows(0).Item("CostCenter").ToString()
        rcbMainCostCenter.SelectedValue = VoucherDatatset.Tables(0).Rows(0).Item("MainCostCenter").ToString()
        rtbRemarks.Text = VoucherDatatset.Tables(0).Rows(0).Item("Remarks").ToString()

        If (Not ((VoucherDatatset.Tables(0).Rows(0).Item("VoucherDate").ToString()) = "") And (VoucherDatatset.Tables(0).Rows(0).Item("VoucherDate").ToString()) IsNot Nothing) Then
            rdpDate.DbSelectedDate = CType(VoucherDatatset.Tables(0).Rows(0).Item("VoucherDate").ToString(), DateTime)
        End If
    End Sub
#End Region

    Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Try
            If rcbType.SelectedIndex = 0 Then
                Label2.Visible = True
                Exit Sub
            Else
                SaveControls()
                CleanTemp() ' This Function is to Clean The Temporary Files Stored by RadAsyncUpload in App_Data\RadUploadTemp
                pnlVoucher.Visible = False
                rgVoucher.Rebind()
                rgVoucher.Visible = True
                RadAjaxManager1.ResponseScripts.Add("radalert('Information Saved Successfully !', 270, 100);")
            End If
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Protected Sub btnCancel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        ResetControls()
        pnlVoucher.Visible = False
        rgVoucher.Rebind()
        rgVoucher.Visible = True
    End Sub

    Sub CleanTemp()
        Dim dir As New DirectoryInfo(Server.MapPath("~\App_Data\RadUploadTemp"))
        Dim file As IO.FileInfo() = dir.GetFiles()
        For i = 0 To file.Length - 1
            file(i).Delete()
        Next
    End Sub

    Protected Sub rgVoucherLines_ItemDataBound(sender As Object, e As Telerik.Web.UI.GridItemEventArgs) Handles rgVoucherLines.ItemDataBound
        If (TypeOf e.Item Is GridEditFormItem AndAlso e.Item.IsInEditMode) And _CommandName <> "InitInsert" Then
            'If (TypeOf e.Item Is GridEditFormItem AndAlso e.Item.IsInEditMode) Then

            Dim editFormItem As GridEditFormItem = CType(e.Item, GridEditFormItem)
            Dim parentItem As GridDataItem = editFormItem.ParentItem

            Dim box As RadComboBox = CType(editFormItem.FindControl("WUCAcc").FindControl("rcbv"), RadComboBox)
            box.SelectedValue = parentItem("AccountNo").Text
            Dim box1 As RadComboBox = CType(editFormItem.FindControl("WUCAcc").FindControl("rcbd"), RadComboBox)
            box1.SelectedValue = parentItem("AccountNo").Text
        End If

    End Sub
End Class
